package com.swak.frame.excel;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSON;
import com.swak.frame.excel.bo.CellObject;
import com.swak.frame.excel.bo.RowObject;
import com.swak.frame.excel.bo.SheetObject;
import com.swak.frame.excel.exception.ExcelException;
import com.swak.frame.logger.SwakLoggerFactory;



public class ExcelHelper {
    private final Logger logger = SwakLoggerFactory.getLogger(getClass());
    private POIExcelHelper poiExcel;

    public static ExcelHelper getInstance() {
        return new ExcelHelper();
    }

    public ExcelHelper() {
        poiExcel = new POIExcelHelper();
    }

    public int openExcel(String path) {
        File excelFile = new File(path);
        return openExcel(excelFile);
    }

    public int openExcel(File excelFile) {
        boolean rtn = poiExcel.openExcel(excelFile);
        int sheetCount = -1;
        if (rtn) {
            sheetCount = poiExcel.getNumberOfSheets();
        }
        return sheetCount;
    }

    public int openExcel(MultipartFile excelFile) {
        boolean rtn = poiExcel.openExcel(excelFile);
        int sheetCount = -1;
        if (rtn) {
            sheetCount = poiExcel.getNumberOfSheets();
        }
        return sheetCount;
    }

    public Map<String, String> getHearder(int sheetIndex) {
        Map<String, String> headerMap = new HashMap<String, String>();
        int startRow = poiExcel.getValidValueFirstRow(sheetIndex);
        int colCount = poiExcel.colCount(sheetIndex, startRow);
        for (int i = 0; i <= colCount; i++) {
            String propName = poiExcel.getAsString(sheetIndex, startRow, i);
            propName = ExcelUtils.matcherLastValue(propName, null);
            if (!StringUtils.isEmpty(propName)) {
                headerMap.put(String.valueOf(i), propName);
            }
        }
        return headerMap;
    }

    public Map<String, String> getHearder(String sheetName) {
        Map<String, String> headerMap = new HashMap<String, String>();
        int startRow = 0;
        int colCount = poiExcel.colCount(sheetName, startRow);
        for (int i = 0; i <= colCount; i++) {
            String propName = poiExcel.getAsString(sheetName, startRow, i);
            propName = ExcelUtils.matcherLastValue(propName, null);
            if (!StringUtils.isEmpty(propName)) {
                headerMap.put(String.valueOf(i), propName);
            }
        }
        return headerMap;
    }


    public List<SheetObject> getInputValuesBySheet(MultipartFile excelFile, String[] notBlankCol)
        throws ExcelException {
        int sheetCount = openExcel(excelFile);
        return handerSheetObject(sheetCount, notBlankCol);
    }

    public List<SheetObject> getInputValuesBySheet(File excelFile, String[] notBlankCol)
        throws ExcelException {
        int sheetCount = openExcel(excelFile);
        return handerSheetObject(sheetCount, notBlankCol);
    }


    public List<SheetObject> getInputValuesBySheet(String excelPath, String[] notBlankCol)
        throws ExcelException {
        if (StringUtils.isEmpty(excelPath)) {
            throw new ExcelException("文件不存在！");// errorId=0
        }
        return getInputValuesBySheet(new File(excelPath), notBlankCol);
    }


    public List<SheetObject> handerSheetObject(int sheetCount, String[] notBlankCol)
        throws ExcelException {
        List<SheetObject> sheetObjectList = new ArrayList<SheetObject>();
        try {
            if (sheetCount < 0) {
                throw new ExcelException("上传的excel没有数据！");// errorId=0
            }
            for (int i = 0; i < sheetCount; i++) {
                SheetObject sheetObject = new SheetObject();
                boolean ishideSheet = poiExcel.getWorkbook().isSheetHidden(i);
                if (ishideSheet) {
                    continue;
                }
                Map<String, String> header = getHearder(i);
                if (header.size() == 0 || header.size() < 2) {
                    throw new ExcelException("上传的excel缺少头部信息！");// errorId=3CMS_EXCEL_TEMPLETE_FORMAT
                }
                int startRow = poiExcel.getValidValueFirstRow(i);
                int rowCount = poiExcel.rowCount(i);
                if (rowCount > startRow) {
                    String sheetName = poiExcel.getSheetNameAt(i);
                    sheetObject.setSheetName(sheetName);
                    sheetObject.setHeader(header);
                    List<RowObject> rowObject =
                        handerRowData(i, startRow, rowCount, header, notBlankCol);
                    sheetObject.setSheetData(rowObject);
                    sheetObjectList.add(sheetObject);
                }
            }
        } catch (Exception e) {
            logger.error("获取数据报错!", e);
            if (e instanceof ExcelException) {
                throw new ExcelException(e.getMessage());
            } else {
                throw new ExcelException("获取excel信息报错！", e);
            }
        } finally {
            poiExcel.close();
        }
        return sheetObjectList;
    }

    public List<RowObject> handerRowData(int sheet, int startRow, int rowCount,
        Map<String, String> header, String[] notBlankCol) throws ExcelException {
        int colCount = header.size();
        List<RowObject> rowDatas = new ArrayList<RowObject>();
        for (int row = startRow + 1; row < rowCount; row++) {
            int[] colName = ExcelUtils.getBlankNameColNumber(header, notBlankCol);
            if (colName.length > 0) {
                for (int i = 0; i < colName.length; i++) {
                    String name = poiExcel.getAsString(sheet, row, colName[i]);
                    if (StringUtils.isEmpty(name)) {
                        throw new ExcelException("第" + row + "行第" + (colName[i] + 1) + "列的值不能为空");
                    }
                }
            }
            RowObject rowData = new RowObject();
            rowData.setRow(row);
            List<CellObject> cellDatas = new ArrayList<CellObject>();
            for (int col = 0; col < colCount; col++) {
                CellObject cellData = new CellObject();
                cellData.setColum(col);
                String cellVal = poiExcel.getAsString(sheet, row, col);
                cellData.setCellVal(cellVal);
                String propName = header.get(String.valueOf(col));
                cellData.setPropName(propName);
                cellDatas.add(cellData);
            }
            rowData.setRowData(cellDatas);
            rowDatas.add(rowData);
        }
        return rowDatas;
    }

    public static void main(String[] args) {
        ExcelHelper excel = ExcelHelper.getInstance();
        try {
            List<SheetObject> sheet =
                excel.getInputValuesBySheet("E:/拼券批量导入模板.xlsx", new String[] {});
            System.out.println(JSON.toJSONString(sheet));
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}
